package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DatabaseManager {

	private static final String insertUserSql = "insert into user(Name,Password,Role,RegisterTime,Email) values(?,?,?,?,?)";
	
	private static final String selectUserByNameSql = "select * from user where Name=?";
	
	private static final String deleteUserByNameSql = "delete from user where Name=?";
	
	private static final String updateUserPassWordByNameSql = "update user set PassWord=? where Name=?";
	
	private static final String updateUserEmailByNameSql = "update user set Email=? where Name=?";
	
	private static final String insertArticleSql = "insert into article(Creator,publishTime,BelongTo,Title) values(?,?,?,?)";
	
	private static final String selectArticleByCreatInfoSql = "select * from article where Creator=? and PublishTime=?";
	
	private static final String selectArticleByArticleIDSql = "select * from article where ID=?";
	
	private static final String selectArticleByBelongToSql = "select * from article where BelongTo=? order by publishTime desc";
	
	private static final String deleteArticleByIDSql = "delete from article where ID=?";
	
	private static final String updateArticleTitleByIDSql = "update article set Title=? where ID=?";
	
	private static final String insertCommentSql = "insert into comment(Commenter,Content,PublishTime,ArticleID) values(?,?,?,?)";
	
	private static final String selectCommentByCreatInfoSql = "select * from comment where commenter=? and publishTime=?";
	
	private static final String selectCommentByArticleIDSql = "select * from comment where ArticleID=? order by publishTime desc";
	
	private static final String deleteCommentByIDSql = "delete from comment where ID=?";
	
	private static final String deleteCommentByArticleIDSql = "delete from comment where ArticleID=?";
	
	private static final String updateCommentContentByIDSql = "update comment set content=? where ID=?";
	
	public static User userQuery(String name) {
		if(name == null) {
			return null;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		ResultSet resultSet = null;
		try {		
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(selectUserByNameSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return null;
			}
			preStatement.setString(1, name);
			resultSet = preStatement.executeQuery();
			if(resultSet == null || !resultSet.first()) {
				preStatement.close();
				JdbcConnection.closeConnection(conn);
				return null;
			}
			User user = new User();
			user.setUserID(resultSet.getInt(1));
			user.setName(resultSet.getString(2));
			user.setPassword(resultSet.getString(3));
			user.setRole(resultSet.getString(4));
			user.setRegisterTime(new Date(resultSet.getLong(5)));
			user.setEmail(resultSet.getString(6));
			resultSet.close();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
			if(resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return new User();
		}
	}
	
	public static boolean userRegister(String name, String password, String role, Date registerTime, String email) {
		if(name == null || password == null || role == null || registerTime == null || email == null) {
			return false;
		}
		if(name.length() > 20 || password.length() > 20 || role.length() > 2 || email.length() > 45) {
			return false;
		}
		User user = userQuery(name);
		if(user != null && !"".equals(user.getName())) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(insertUserSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setString(1, name);
			preStatement.setString(2, password);
			preStatement.setString(3, role);
			preStatement.setLong(4, registerTime.getTime());
			preStatement.setString(5, email);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	/**
	 * 
	 * @param name
	 * @param password
	 * @return User 
	 */
	public static User userCheckValidity(String name, String password) {
		if(name == null || password == null) {
			return null;
		}
		if(name.length() > 20 || password.length() > 20) {
			return null;
		}
		User user = userQuery(name);
		if(user == null || "".equals(user.getName())) {
			return null;
		}
		if(!password.equals(user.getPassword())) {
			return null;
		}
		return user;
	}
	
	public static boolean userChangePassWord(String name, String password) {
		if(name == null || password == null) {
			return false;
		}
		if(name.length() > 20 || password.length() > 20) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(updateUserPassWordByNameSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setString(1, password);
			preStatement.setString(2, name);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	
	public static boolean userChangeEmail(String name, String email) {
		if(name == null || email == null) {
			return false;
		}
		if(name.length() > 20 || email.length() > 45) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(updateUserEmailByNameSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setString(1, email);
			preStatement.setString(2, name);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	
	public static boolean userDelete(String name, String password) {
		if(name == null || password == null) {
			return false;
		}
		if(name.length() > 20 || password.length() > 20) {
			return false;
		}
		if(userCheckValidity(name, password) == null) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(deleteUserByNameSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setString(1, name);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	
	public static boolean articlePublish(String creator, Date publishTime, int belongTo, String title) {
		if(creator == null || publishTime == null || title == null) {
			return false;
		}
		if(creator.length() > 20 || title.length() > 255) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(insertArticleSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setString(1, creator);
			preStatement.setLong(2, publishTime.getTime());
			preStatement.setInt(3, belongTo);
			preStatement.setString(4, title);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	public static Article articleQueryByArticleID(int articleID) {
		if(articleID <= 0) {
			return null;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		ResultSet resultSet = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(selectArticleByArticleIDSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return null;
			}
			preStatement.setInt(1, articleID);
			resultSet = preStatement.executeQuery();
			if(resultSet == null || !resultSet.first()) {
				preStatement.close();
				JdbcConnection.closeConnection(conn);
				return null;
			}
			Article article = new Article();
			article.setArticleID(resultSet.getInt(1));
			article.setCreator(resultSet.getString(2));
			article.setPublishTime(new Date(resultSet.getLong(3)));
			article.setBelongTo(resultSet.getInt(4));
			article.setTitle(resultSet.getString(5));
			resultSet.close();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return article;
		} catch (SQLException e) {
			e.printStackTrace();
			if(resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return null;
		}
	}
	public static Article articleQueryByCreatInfo(String creator, Date publishTime) {
		if(creator == null || publishTime == null) {
			return null;
		}
		if(creator.length() > 20) {
			return null;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		ResultSet resultSet = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(selectArticleByCreatInfoSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return null;
			}
			preStatement.setString(1, creator);
			preStatement.setLong(2, publishTime.getTime());
			resultSet = preStatement.executeQuery();
			if(resultSet == null || !resultSet.first()) {
				preStatement.close();
				JdbcConnection.closeConnection(conn);
				return null;
			}
			Article article = new Article();
			article.setArticleID(resultSet.getInt(1));
			article.setCreator(resultSet.getString(2));
			article.setPublishTime(new Date(resultSet.getLong(3)));
			article.setBelongTo(resultSet.getInt(4));
			article.setTitle(resultSet.getString(5));
			resultSet.close();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return article;
		} catch (SQLException e) {
			e.printStackTrace();
			if(resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return null;
		}
	}
	
	public static List<Article> articleQueryByBelongTo(int belongTo) {
		List<Article> articleList = new ArrayList<Article>();
		Connection conn = null;
		PreparedStatement preStatement = null;
		ResultSet resultSet = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(selectArticleByBelongToSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return null;
			}
			preStatement.setInt(1, belongTo);
			resultSet = preStatement.executeQuery();
			if(resultSet == null) {
				preStatement.close();
				JdbcConnection.closeConnection(conn);
				return null;
			}
			while(resultSet.next()) {
				Article article = new Article();
				article.setArticleID(resultSet.getInt(1));
				article.setCreator(resultSet.getString(2));
				article.setPublishTime(new Date(resultSet.getLong(3)));
				article.setBelongTo(resultSet.getInt(4));
				article.setTitle(resultSet.getString(5));
				articleList.add(article);
			}
			resultSet.close();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return articleList;
		} catch (SQLException e) {
			e.printStackTrace();
			if(resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return null;
		}
	}
	
	public static boolean articleChangeTitle(int articleID, String title) {
		if(title == null) {
			return false;
		}
		if(title.length() > 255) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(updateArticleTitleByIDSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setString(1, title);
			preStatement.setInt(2, articleID);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}
	}
	
	public static boolean articleDelete(int articleID) {
		if(!commentDeleteByArticleID(articleID)) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(deleteArticleByIDSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setInt(1, articleID);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	
	public static boolean commentPublish(String commenter, String content, Date publishTime, int articleID) {
		if(commenter == null || content == null || publishTime == null) {
			return false;
		}
		if(commenter.length() > 20) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(insertCommentSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setString(1, commenter);
			preStatement.setString(2, content);
			preStatement.setLong(3, publishTime.getTime());
			preStatement.setInt(4, articleID);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}
	}
	
	public static Comment commentQueryByCreatInfo(String commenter, Date publishTime) {
		if(commenter == null || publishTime == null) {
			return null;
		}
		if(commenter.length() > 20) {
			return null;
		}
		Connection conn = null;
		PreparedStatement preStatement = null;
		ResultSet resultSet = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(selectCommentByCreatInfoSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return null;
			}
			preStatement.setString(1, commenter);
			preStatement.setLong(2, publishTime.getTime());
			resultSet = preStatement.executeQuery();
			if(resultSet == null || !resultSet.first()) {
				preStatement.close();
				JdbcConnection.closeConnection(conn);
				return null;
			}
			Comment comment = new Comment();
			comment.setCommentID(resultSet.getInt(1));
			comment.setCommenter(resultSet.getString(2));
			comment.setContent(resultSet.getString(3));
			comment.setPublishTime(new Date(resultSet.getLong(4)));
			comment.setArticleID(resultSet.getInt(5));
			resultSet.close();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return comment;
		} catch (SQLException e) {
			e.printStackTrace();
			if(resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return null;
		}
	}
	
	public static List<Comment> commentQueryByArticleID(int articleID) {
		List<Comment> commentList = new ArrayList<Comment>();
		Connection conn = null;
		PreparedStatement preStatement = null;
		ResultSet resultSet = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(selectCommentByArticleIDSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return null;
			}
			preStatement.setInt(1, articleID);
			resultSet = preStatement.executeQuery();
			if(resultSet == null) {
				preStatement.close();
				JdbcConnection.closeConnection(conn);
				return null;
			}
			while(resultSet.next()) {
				Comment comment = new Comment();
				comment.setCommentID(resultSet.getInt(1));
				comment.setCommenter(resultSet.getString(2));
				comment.setContent(resultSet.getString(3));
				comment.setPublishTime(new Date(resultSet.getLong(4)));
				comment.setArticleID(resultSet.getInt(5));
				commentList.add(comment);
			}
			resultSet.close();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return commentList;
		} catch (SQLException e) {
			e.printStackTrace();
			if(resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return null;
		}
	}
	
	public static boolean commentDeleteByID(int commentID) {
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(deleteCommentByIDSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setInt(1, commentID);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	
	public static boolean commentDeleteByArticleID(int ArticleID) {
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(deleteCommentByArticleIDSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setInt(1, ArticleID);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	
	public static boolean commentChangeContent(int commentID, String content) {
		Connection conn = null;
		PreparedStatement preStatement = null;
		try {
			conn = JdbcConnection.getConnection();
			preStatement = conn.prepareStatement(updateCommentContentByIDSql);
			if(preStatement == null)
			{
				JdbcConnection.closeConnection(conn);
				return false;
			}
			preStatement.setString(1, content);
			preStatement.setInt(2, commentID);
			preStatement.execute();
			preStatement.close();
			JdbcConnection.closeConnection(conn);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			if(preStatement != null) {
				try {
					preStatement.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			JdbcConnection.closeConnection(conn);
			return false;
		}	
	}
	
	public static void main(String[] args) {
		//DatabaseManager.userRegister("test", "test", "A", new Date(System.currentTimeMillis()), "hello@gmail.com");
		//User user = DatabaseManager.userQuery("test");
		//System.out.println(user.getUserID() + " " + user.getName() + " " + user.getPassword() + " " + user.getRole() + " " + user.getRegisterTime() + " " + user.getEmail());
		//DatabaseManager.userChangePassWord("test", "rest");
		//DatabaseManager.userChangeEmail("test", "hello@163.com");
		//System.out.println(DatabaseManager.userCheckValidity("test", "test"));
		//DatabaseManager.userDelete("test", "rest");
		//DatabaseManager.articlePublish("test", new Date(System.currentTimeMillis()), 2, "hello");
		/*List<Article> articleList = DatabaseManager.articleQueryByBelongTo(2);
		for(int i = 0; i < articleList.size(); i++)
		{
			System.out.println(articleList.get(i).getArticleID() + " " + articleList.get(i).getCreator() + " " + articleList.get(i).getPublishTime() + " " + articleList.get(i).getBelongTo());
		}*/
		//DatabaseManager.articleDelete(1);
		/*Article article = DatabaseManager.articleQueryByCreatInfo("test", new Date(new Long("1314411797295")));
		System.out.println(article.getArticleID() + " " + article.getCreator() + " " + article.getPublishTime() + " " + article.getBelongTo());*/
		//DatabaseManager.commentPublish("test", "bad", new Date(System.currentTimeMillis()), 2);
		/*List<Comment> commentList = DatabaseManager.commentQueryByArticleID(2);
		for(int i = 0; i < commentList.size(); i++) {
			System.out.println(commentList.get(i).getCommentID() + " " + commentList.get(i).getContent() + " " + commentList.get(i).getCommenter() + " " + commentList.get(i).getPublishTime() + " " + commentList.get(i).getArticleID());
		}*/
		/*Comment comment = DatabaseManager.commentQueryByCreatInfo("test", new Date(new Long("1314412573578")));
		System.out.println(comment.getCommentID() + " " + comment.getContent() + " " + comment.getCommenter() + " " + comment.getPublishTime() + " " + comment.getArticleID());
		*/
		//DatabaseManager.commentChangeContent(2, "middle");
		//DatabaseManager.commentDelete(1);
		//DatabaseManager.articlePublish("测试", new Date(System.currentTimeMillis()), 2, "测试");
	}

}
